package com.kla.power.mapper;

import com.kla.power.entity.InverterAmountStatisticsDayEntity;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.kla.power.entity.StationEntity;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * 逆变器电量统计，每日凌晨更新昨日发电量，用于显示逆变器详情页 Mapper 接口
 * </p>
 * @author WuZH
 * @since 2024-06-02
 */
@Mapper
public interface InverterAmountStatisticsDayMapper extends BaseMapper<InverterAmountStatisticsDayEntity> {


    @Select (value = "select * from (select * from inverter_amount_statistics_day_info where inverterSn=#{inverterAmountStatisticsDayEntity.inverterSn}) isi order by isi.createTime desc limit 1")
    InverterAmountStatisticsDayEntity querySingle (@Param ("inverterAmountStatisticsDayEntity") InverterAmountStatisticsDayEntity inverterAmountStatisticsDayEntity);


    /**
     * 查询一个商户下所有电站的所有设备的指定时间段内的发电量
     * @return
     */
    @Select ("<script>SELECT iasdi.yesterdayPower,iasdi.inverterSn,date(iasdi.collectorTime) AS collectorTime, iasdi.monthTime FROM inverter_amount_statistics_day_info iasdi LEFT JOIN device_info di ON iasdi.inverterSn = di.deviceSn\n" +
            "LEFT JOIN station_info si ON di.stationId = si.id\n" +
            "<trim prefix=\"WHERE\" prefixOverrides=\"and | or\">\n" +
            "<if test='stationEntity.startTime!=null'> AND date_format(iasdi.collectorTime, '%Y-%m-%d') &gt;= date_format(#{stationEntity.startTime}, '%Y-%m-%d') </if>\n" +
            "<if test='stationEntity.endTime!=null'> AND date_format(iasdi.collectorTime, '%Y-%m-%d') &lt;= date_format(#{stationEntity.endTime}, '%Y-%m-%d') </if>\n" +
            "<if test='stationEntity.merchantId!=null'> AND " +
            "si.merchantId IN (SELECT id\n" +
            "                        FROM (SELECT t1.id,\n" +
            "                                     t1.parentId,\n" +
            "                                     IF(find_in_set(parentId, @pids) > 0, @pids := concat(@pids, ',', id), 0) AS ischild\n" +
            "                              FROM (SELECT id, parentId FROM merchant_info t) t1,\n" +
            "                                   (SELECT @pids:=#{stationEntity.merchantId}) t2 ) t3 where ischild != 0\n" +
            "                                    UNION\n" +
            "                                    SELECT id\n" +
            "                                    FROM merchant_info pi\n" +
            "                                    WHERE pi.id=#{stationEntity.merchantId})" +
            " </if>\n" +
            "<if test='stationEntity.id!=null'> AND si.id=#{stationEntity.id} </if>\n" +
            "</trim></script>")
    List<InverterAmountStatisticsDayEntity> queryAllStationAmountList (@Param ("stationEntity") StationEntity stationEntity);


    /**
     * 查询一个电站下所有逆变器历史发电量
     * @param stationEntity
     * @return
     */
    @Select (value = "SELECT * FROM inverter_amount_statistics_day_info iasdi\n" +
            "LEFT JOIN device_info di on iasdi.inverterSn = di.deviceSn\n" +
            "LEFT JOIN station_info si on si.id = di.stationId\n" +
            "WHERE si.id=#{stationEntity.id}\n" +
            "AND date_format(iasdi.collectorTime, '%Y-%m-%d') >= date_format(#{stationEntity.startTime}, '%Y-%m-%d') " +
            "AND date_format(iasdi.collectorTime, '%Y-%m-%d') <= date_format(#{stationEntity.endTime}, '%Y-%m-%d') ")
    List<InverterAmountStatisticsDayEntity> queryAmountByStation (@Param ("stationEntity") StationEntity stationEntity);


    /**
     * 查询逆变器按月统计每天的发电量
     * @param inverterAmountStatisticsDayEntity
     * @return
     */
    @Select (value = "<script>SELECT * FROM inverter_amount_statistics_day_info iai " +
            "<trim prefix=\"WHERE\" prefixOverrides=\"and | or\">" +
            "<if test='inverterAmountStatisticsDayEntity.inverterSn!=null'> AND iai.inverterSn=#{inverterAmountStatisticsDayEntity.inverterSn} </if>" +
            " AND date_format(iai.collectorTime, '%Y-%m-%d') &gt;= date_format(#{inverterAmountStatisticsDayEntity.startTime}, '%Y-%m-%d') AND date_format(iai.collectorTime, '%Y-%m-%d') &lt;= date_format(#{inverterAmountStatisticsDayEntity.endTime}, '%Y-%m-%d') " +
            "</trim></script>")
    List<InverterAmountStatisticsDayEntity> queryHistoryList (@Param ("inverterAmountStatisticsDayEntity") InverterAmountStatisticsDayEntity inverterAmountStatisticsDayEntity);


    /**
     * 查询一台逆变器上个月发电量列表
     * @param inverterAmountStatisticsDayEntity
     * @return
     */
    @Select (value = "SELECT * FROM inverter_amount_statistics_day_info iasdi WHERE iasdi.inverterSn=#{inverterAmountStatisticsDayEntity.inverterSn} AND iasdi.monthTime=#{inverterAmountStatisticsDayEntity.monthTime}")
    List<InverterAmountStatisticsDayEntity> queryLastMonth (@Param ("inverterAmountStatisticsDayEntity") InverterAmountStatisticsDayEntity inverterAmountStatisticsDayEntity);

    /**
     * 日报表
     * @param inverterAmountStatisticsDayEntity
     * @return
     */
    @Select (value = "<script>select di.deviceSn,iasdi.yesterdayPower,si.stationName,di.power,di.deviceTitle,iasdi.createTime,iasdi.inverterSn\n" +
            "from inverter_amount_statistics_day_info iasdi\n" +
            "left join device_info di on iasdi.inverterSn=di.deviceSn\n" +
            "left join station_info si on di.stationId=si.id\n" +
            "<trim prefix=\"WHERE\" prefixOverrides=\"and | or\">" +
            "<if test='inverterAmountStatisticsDayEntity.createTime!=null'> AND date_format(iasdi.createTime,'%Y-%m-%d')=date_format(#{inverterAmountStatisticsDayEntity.createTime},'%Y-%m-%d') </if>" +
            "</trim>order by iasdi.createTime desc LIMIT #{inverterAmountStatisticsDayEntity.page},#{inverterAmountStatisticsDayEntity.limit}</script>")
    List<InverterAmountStatisticsDayEntity> queryDay (@Param ("inverterAmountStatisticsDayEntity") InverterAmountStatisticsDayEntity inverterAmountStatisticsDayEntity);
    @Select (value = "<script>select COUNT(1)\n" +
            "from inverter_amount_statistics_day_info iasdi\n" +
            "left join device_info di on iasdi.inverterSn=di.deviceSn\n" +
            "left join station_info si on di.stationId=si.id\n" +
            "<trim prefix=\"WHERE\" prefixOverrides=\"and | or\">" +
            "<if test='inverterAmountStatisticsDayEntity.createTime!=null'> AND date_format(iasdi.createTime,'%Y-%m-%d')=date_format(#{inverterAmountStatisticsDayEntity.createTime},'%Y-%m-%d') </if>" +
            "</trim></script>")
    Integer queryDayCount (@Param ("inverterAmountStatisticsDayEntity") InverterAmountStatisticsDayEntity inverterAmountStatisticsDayEntity);

    /**
     * 下载所有日报表，不带分页
     * @param inverterAmountStatisticsDayEntity
     * @return
     */
    @Select (value = "<script>select di.deviceSn,iasdi.yesterdayPower,si.stationName,di.power,di.deviceTitle,iasdi.createTime,iasdi.inverterSn\n" +
            "from inverter_amount_statistics_day_info iasdi\n" +
            "left join device_info di on iasdi.inverterSn=di.deviceSn\n" +
            "left join station_info si on di.stationId=si.id\n" +
            "<trim prefix=\"WHERE\" prefixOverrides=\"and | or\">" +
            "<if test='inverterAmountStatisticsDayEntity.createTime!=null'> AND date_format(iasdi.createTime,'%Y-%m-%d')=date_format(#{inverterAmountStatisticsDayEntity.createTime},'%Y-%m-%d') </if>" +
            "</trim></script>")
    List<InverterAmountStatisticsDayEntity> downDay (@Param ("inverterAmountStatisticsDayEntity") InverterAmountStatisticsDayEntity inverterAmountStatisticsDayEntity);

    /**
     * 月报表
     * @param inverterAmountStatisticsDayEntity
     * @return
     */
    @Select (value = "<script>select di.deviceSn,iasdi.yesterdayPower,iasdi.inverterSn,iasdi.collectorTime,si.stationName,di.power,di.deviceTitle,iasdi.createTime,day(iasdi.collectorTime) AS day\n" +
            "from inverter_amount_statistics_day_info iasdi\n" +
            "left join device_info di on iasdi.inverterSn=di.deviceSn\n" +
            "left join station_info si on di.stationId=si.id\n" +
            "<trim prefix=\"WHERE\" prefixOverrides=\"and | or\">" +
            "<if test='inverterAmountStatisticsDayEntity.startTime!=null'> AND date_format(iasdi.collectorTime,'%Y-%m-%d')&gt;=date_format(#{inverterAmountStatisticsDayEntity.startTime},'%Y-%m-%d') </if>" +
            "<if test='inverterAmountStatisticsDayEntity.endTime!=null'> AND date_format(iasdi.collectorTime,'%Y-%m-%d')&lt;=date_format(#{inverterAmountStatisticsDayEntity.endTime},'%Y-%m-%d') </if>" +
            "</trim>order by iasdi.createTime desc</script>")
    List<InverterAmountStatisticsDayEntity> queryMonth (@Param ("inverterAmountStatisticsDayEntity") InverterAmountStatisticsDayEntity inverterAmountStatisticsDayEntity);

    /**
     * 年报表
     * @param inverterAmountStatisticsDayEntity
     * @return
     */
    @Select ( value =
            "select iasdi.inverterSn,\n" +
            "       iasdi.yesterdayPower,\n" +
            "       iasdi.collectorTime,\n" +
            "       si.stationName,\n" +
            "       di.power,\n" +
            "       iasdi.monthTime,\n" +
            "       iasdi.month,\n" +
            "       di.deviceTitle,\n" +
            "       iasdi.createTime\n" +
            "from inverter_amount_statistics_day_info iasdi\n" +
            "         left join device_info di on iasdi.inverterSn = di.deviceSn\n" +
            "         left join station_info si on di.stationId = si.id\n" +
            "WHERE (YEAR(iasdi.collectorTime) = #{inverterAmountStatisticsDayEntity.year} AND MONTH(iasdi.collectorTime) > 0)\n" +
            "  AND (YEAR(iasdi.collectorTime) = #{inverterAmountStatisticsDayEntity.year} AND MONTH(iasdi.collectorTime) <= 12)\n" +
            "order by iasdi.createTime desc")
    List<InverterAmountStatisticsDayEntity> queryYear (@Param ("inverterAmountStatisticsDayEntity") InverterAmountStatisticsDayEntity inverterAmountStatisticsDayEntity);

}
